Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# GROUPINGID

The GROUPINGID command populates a previously-defined object with the grouping ids for the values of a hierarchical dimension. A grouping id is a numeric value that corresponds to a level of a hierarchical dimension. The grouping id for the lowest-level of the hierarchy is `0` (zero).

Grouping ids are especially useful for identifying values of different levels of a hierarchical dimension. Dimension values in the same level of the hierarchy have the same value for their grouping id. Selecting dimension values for a specific level is easier with grouping ids because the desired values can be identified with a single condition of groupingid = n.

Typically, you use a GROUPINGID statement when you are planning on accessing analytic workspace data in SQL using the `OLAP_TABLE` function. For more information on the `OLAP_TABLE` function see the Oracle OLAP Reference.

See also: The `GROUPING_ID` function in Oracle Database SQL Reference for more information on grouping ids.

Syntax

GROUPINGID [family-relation] INTO destination-object -

{USING level-relation} [INHIERARCHY {inh-variable | inh-valueset}] [LEVELORDER lo-valueset]

where destination-object is one of the following:

  grouping-relation
  grouping-variable
  grouping-surrogate

Arguments

family-relation

  A self-relation for a hierarchical dimension. This self-relation is dimensioned by a hierarchical dimension. The values of the self-relation are the parents of each value in the hierarchical dimension. The family-relation argument is optional only when you use the GROUPINGID statement to populate a surrogate and the GROUPINGID statement includes a LEVELORDER clause.

grouping-relation

  The name of a previously-defined relation. One of the dimensions of grouping-relation must be the hierarchical dimension. The values of grouping-relation are calculated and populated when the GROUPINGID statement executes. See DEFINE RELATION for information on defining relations.

grouping-variable

  The name of a previously-defined numeric variable. One of the dimensions of grouping-variable must be the hierarchical dimension. The data type of grouping-variable can be any numeric type including `NUMBER`. The values of grouping-variable are calculated and populated when the GROUPINGID statement executes.See DEFINE VARIABLE for information on defining variables.

grouping-surrogate

  The name of a previously-defined surrogate for the hierarchical dimension. The values of grouping-surrogate are calculated and populated when the GROUPINGID statement executes. See DEFINE SURROGATE for information on defining surrogates.

USING

  Specifies that the level of the values of the hierarchical dimension are to be considered when creating grouping ids.

level-relation

  A relation that is dimensioned by the hierarchical dimension. For each value of the hierarchical dimension, the relation has its value the name of the level for the dimension's value.

INHIERARCHY

  Specifies that only some of the values of the hierarchical dimension are to be considered when creating grouping ids.

inh-variable

  A BOOLEAN variable that is dimensioned by the hierarchical dimension and, when the hierarchical dimension is a multi-hierarchical dimension, by a dimension that is the names of the hierarchies. The values of the variable are `TRUE` when the dimension value is in a hierarchy and `FALSE` when it is not.

inh-valueset

  The name of a valueset object whose values identify the hierarchical dimension values to be considered when creating grouping ids. Values not included in the valueset are ignored.

LEVELORDER

  Specifies the top-down order of the levels when creating grouping ids.

lo-valueset

  The name of a valueset object whose values are the names of the levels to be used when creating grouping ids. The order of the values in the valueset object determine the grouping id assigned.

Examples

Example 14-5 Using GROUPINGID to Populate a Variable with Grouping Ids

Assume that you have the following objects in your analytic workspace.

    DEFINE geography DIMENSION TEXT WIDTH 12
    LD Geography Dimension Values
    DEFINE geography.parent RELATION geography <geography>
    LD Child-parent relation for geography
    DEFINE geography.hierarchyid DIMENSION INTEGER
    LD Dimension whose values are ids for hierarchies in geography

To create a grouping id variable for the `Standard` hierarchy of geography, define a child-parent relation of only those values that are in the hierarchy whose grouping ids you want to generate, and define a variable to hold the grouping ids. Examples of these definitions follow.

    DEFINE geog.gid INTEGER VARIABLE <geography>
    DEFINE geography.newparent RELATION geography <geography>

Then populate these variables using statements similar to these.

    AW DETACH myaw
    AW ATTACH myaw ro
    PUSH OKNULLSTATUS
    OKNULLSTATUS = TRUE
    " Populate the child-parent relation for hierarchy 1
    geography.newparent = geography.parent(geography.hierarchyid 1)
    " Populate the grouping id variables
    GROUPINGID geography.newparent INTO geog.gid
    " Save changes to analytic workspace
    POP OKNULLSTATUS
    ALLSTAT
    UPDATE
    COMMIT

Reports for the new objects created by this code (geography.newparent and GEOG.GID) follow.

    REPORT geography.newparent

    GEOGRAPHY          GEOGRAPHY.NEWPARENT
    ---------------- ----------------
    World            NA
    Americas         World
    Canada           Americas
    Toronto          Canada
    Montreal         Canada
    Ottawa           Canada
    Vancouver        Canada
    Edmonton         Canada
    Calgary          Canada
    Usa              Americas
    Boston           Usa
    Losangeles       Usa
    Dallas           Usa
    Denver           Usa
    Newyork          Usa
    Chicago          Usa
    Seattle          Usa
    Mexico           Americas
    ...              ...
    Japan            Asia
    Tokyo            Japan
    Osaka            Japan
    Kyoto            Japan
    China            Asia
    Beijing          China
    Shanghai         China
    ...              ...
    India            Asia
    Ireland          Europe
    Taiwan           Asia
    Thailand         Asia

    REPORT geog.gid
    GEOGRAPHY            GEOG.GID
    ---------------- ----------------
    World                           7
    Americas                        3
    Canada                          1
    Toronto                         0
    Montreal                        0
    Ottawa                          0
    Vancouver                       0
    Edmonton                        0
    Calgary                         0
    Usa                             1
    Boston                          0
    Losangeles                      0
    Dallas                          0
    Denver                          0
    Newyork                         0
    Chicago                         0
    Seattle                         0
    Mexico                          1
    ...                             ...
    Japan                           1
    Tokyo                           0
    Osaka                           0
    Kyoto                           0
    China                           1
    Beijing                         0
    Shanghai                        0
    ...                             ...
    India                           1
    Ireland                         1
    Taiwan                          1
    Thailand                        1